|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest
experts as well as their commentary on experiences with
new database technologies. Have some views of your own to
share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Jul
28
Written by:
QCTO Blog
Tuesday, July 28, 2009
Written by Finn Ellebaek Nielsen
Introduction
In my previous blog post
I covered test policy and strategy. In this blog post I'll dig a little
deeper and elaborate on best practices for the test approach.
Test Approach
The test approach is the implementation details of the test policy and strategy for a particular project.
My suggestions for a test approach are the following:
- Quest Code Tester for Oracle is used for testing the Oracle code.
- Change handling:
- No changes should be applied to the code
without a set of automated test cases covering the area to be changed,
serving as a safety net. The tests can be placed at various levels (eg
unit and integration) and these need to be in place in order to ensure
that no unintended and unwanted behavioral change is introduced. They
should be run before and after any change and it should be verified that
they are all successful (status "green").
- For various reasons you may find that the
code you need to change is not testable so you cannot put test cases
around it without modifying it. This is also referred to as the "legacy
code dilemma". A solution to this dilemma would be to introduce tests at
a higher level (eg integration) before changing the code to be
testable, verify the new tests, and then introduce unit tests and verify
the integration tests.
- If the change is necessary in order to fix a
defect, then a test case that reproduces the defect should be added to
the unit/integration test and executed/verified before the change is
done. This ensures that the same defect isn't reintroduced in the
future.
- Realistic standards for the following should be established (I will cover this in further detail in a future blog post):
- Code Coverage (CC). The percentage of number
of statements executed through the test, out of the total number of
statements in the program/subprogram. It doesn't make sense to opt for
100% CC in the units that will be tested automatically as that is often
not realistic due to the sheer volume of permutations of input parameter
values and test data.
- Boundary tests based on parameter datatype. Some of these can be generated automatically by Quest Code Tester for Oracle.
- A library of reusable components that handle test
data setup should be established and shared as widely as possible on
the development team. Data setup is one of the biggest challenges in
database program testing.
- Informal code review should be carried out on a
regular basis, eg just before committing a change to the source code
version control system. This will help identify defects and also help
ensure that the corporate style guide and code conventions are followed
(in case this cannot be automated though development tools).
- Database environments:
- Development and unit testing: One for each
developer. This can either be server-based or running on the developer's
own PC. Cheap Oracle desktop license options are:
- Oracle Database 10g Express
Edition (XE). Free. Running Windows and Linux only, doesn't support Java
stored procedures (including some of the UTL% and DBMS% Oracle
packages) and is "only" 10g. You can only have one instance per
machine and there are limitations on the amount of memory that can be
accessed (1GB) and also on the size of your database instance (4GB).
Furthermore, it can use one CPU only.
- Oracle Database x.y Personal Edition
(PE). Cheap (about USD 460). Running Windows only. Includes all the
Enterprise Edition options apart from Real Application Cluster.
- Oracle Database x.y Standard Edition One
(SE1). Cheap (about USD 900). Running on all the supported Oracle
platforms. Includes no Enterprise Edition options and is only supported
on machines with at most two CPU sockets.
- Test (integration test): Continuous Integration, which is described in a separate section later in this post.
- System test (user acceptance test).
- Production: Are you deploying some of your
tests to the production environment? Why not? The future at Microsoft
seems to be heading that way as they have realized that modern software
is very complicated and it can be difficult to reproduce
environment-specific defects. This subject is "religious" and people
seem to have strong opinions about the right and wrong. In my view
there's nothing wrong with having some kinds of tests in production,
such as smoke tests to verify that a production deployment was
successful. Obviously, it should be ensured that they cannot
modify production data.
- The automated unit tests should run in just a few
minutes per unit. If they take too long time to run they will not be
run frequently enough. Some test experts in other programming languages
such as Java and C# claim that all the tests (at every level) must
execute in as little as 10 minutes or even 10 seconds. In my view the
latter is not realistic, especially with an Oracle database. Integration
and regression tests may take longer to run, which is not a problem as
they're likely to be run over night.
- The test cases are not static and will need continuous changes/enhancements.
Database Environments
You may wonder why I suggest that each developer has
their own database. An Oracle database is all about collaboration, isn't
it? That's true, but this is not necessarily a good idea. Consider this
situation: Your application has a central package that most other
PL/SQL and SQL code depend on. Now you need to make a change to it,
which presents you with the following challenges:
- You cannot compile it if some code using the package is currently running in another session as it will be locked.
- You need to make sure that you implement the change correctly:
- If you get the syntax wrong you have just
severely affected all the dependent code such that it no longer compiles
(if the package specification has compilation errors) or runs (if the
package body has compilation errors).
- If you get the semantics wrong it will take
you a while to run your unit tests against it and find and fix the
error. In the meantime, all your fellow team members are ripping their
hair out in frustration because they think that they have a defect in
the code they're currently working on.
- Two developers need to make changes to different
subprograms of the package. This cannot be achieved in the same schema
of the same instance and if they need to share the Code Tester
repository in the same schema of the same instance Test Builder/Test
Editor locks the entire test definition when the first developer enters
it, blocking out the other developer.
- It can be difficult to control and cater for
synonyms, sequences, PL/SQL and views in automated testing as these are
shared database objects (instead of session-based). When you have your
own instance you can control them 100%. Future blog post(s) will
describe how this can benefit you.
DBA Privileges
The following limitations may also slow your progress
if you share the database with others and DBAs are controlling the
environment, not giving you any DBA privileges. In fact, each developer
should be the DBA of their own development database because:
- You should be able to kill defunct/infinite loop processes.
- You should be able to watch progress of long running transactions and sessions (access to V$SESSION_LONGOPS and V$SESSION).
- You should be able to create schemas for test (eg
for Code Tester's repository and test code) and prototype (trying out
new ideas) purposes.
- Have easy access to alert log and trace files.
- The DBAs don't have time to maintain your database.
Oracle Database Edition
My suggestion is that each developer gets a PE license
of the same Oracle version as the "central" instances. A PE license is
very cheap, XE is severely limited and may be older than your "central"
instances (it is rumored that 11g XE will be released some time after Oracle Database 11g
Release 2). Furthermore, SE1 doesn't come with any EE options, which PE
does. If you don't run Windows on your desktop you would have to
consider XE or SE1 instead.
Another solution would be to provide a set of
application schemas to each developer such that all the developers use
the same instance but have their own sandboxes. This can be cumbersome
to control as synonyms would have to control which schemas are to be
used in cross-schema object dependencies. Perhaps the other schema names
are hard-coded in the application logic and perhaps the application
doesn't work correctly when not running in a schema with a specific
name.
Quest Code Tester for Oracle Repository
So how do you share the Code Tester unit test
definitions? All the test definitions should be exported and committed
to the source code version control system anyway, from which they can be
imported into the other developer's databases.
Synchronizing Data and Code
Doesn't each developer's own database instance get out
of synch with the "central" databases and what about access to
up-to-date production data?
- The purpose of the development environment is to
develop and run unit tests and the unit tests must ensure that test data
is set up and torn down when they run so no production data is
necessary.
- A mechanism that deploys other developers' committed changes since a given time should be in place (can be tricky to implement).
- The development environment can be cloned from
another database instance provided that they run the same Oracle version
and character/national character set, even across platforms. It's
possible to do this in a way that ensures that the instance-specific
privileges and data of the development environment are kept. I've
successfully co-developed such a facility for a large hedge fund in
London.
Continuous Integration
Continuous Integration (CI) is the practice of
integrating early and often. In our case it means that the test
environment should automatically be updated each night with changes
committed to your source code version control system and after this the
entire test suite is run. This can be tricky and is the subject of a
future blog post.
CI is a crucial element in the test approach:
- If code is developed requiring privileges or
access to objects that are not granted in any of the "central"
environments (test, UAT, pre prod, prod etc) this will soon be
discovered when the code is committed to the version control system as
that night the CI will expose that the code doesn't compile or run.
- The integration tests that are run by CI will
check whether the integration works correctly. This is necessary even
though the component changed is fully covered by unit tests as the
component could still be called or used in an incorrect way. The changes
should only be committed to the version control system after the unit
tests have been successfully executed.
Future Blog Posts
Future blog post will cover related issues like:
- Testing effort/depth required -- how much testing do we need?
- Test design tips & tricks.
- How to break dependencies through creation/usage of mock objects in automated Oracle code testing.
- How to apply Test-Driven Development to software projects using Oracle code.
- Continuous Integration.
- Code Coverage.
References
- Working Effectively with Legacy Code by Michael C. Feathers, Prentice Hall, 2004. ISBN 978-0131177055.
- Implementing Automated Software Testing by
Elfriede Dustin, Thom Garrett and Bernie Gauf, Addison Wesley, 2009.
ISBN 978-0321580511.
- Foundations of Software Testing: ISTQB
Certification by Dorothy Graham, Isabel Evans, Erik Van Veenendaal and
Rex Black, Cengage, 2008. ISBN 978-1844809899.
3 comment(s) so far...
Re: Test Approach
Hi Finn,
Thanks for your posts, the blog is very interesting and useful.
You wrote that "data setup is one of the biggest challenges in database
program testing." Indeed it looks as a big problem for me. We are doing
custom development for Oracle E-Business Suite (OEBS) and I'd like to
introduce testing in my company. Okay, to test SQL queries one needs to
create rather small data set, data integrity kept in mind. But most of
the tables are standard ones of OEBS, and all OEBS integrity rules are
on application level (not in database). It's extremely difficult to
correctly fill dozens of tables; it's also difficult and time-consuming
via APIs. So I'm in doubt if the testing makes sense.
I'll greatly appreciate If you can point me to any information on data
setup techniques.
Thanks,
Egor.
By Egor on
Wednesday, September 30, 2009
|
Re: Test Approach
Hi Egor.
Thanks a lot for your comments.
I'll come back to this in more detail in a future blog post but for now I
can give you this information:
1. I've successfully implemented test data setup using various XML
techniques: DBMS_XMLGEN to generate XML based on a SELECT statement,
DBMS_XMLSTORE to INSERT, UPDATE and DELETE data. The XML data is then
the master data of the data setup and it is easy to save and edit.
2. I've also successfully implemented automated unit testing against
OEBS some years ago. This was in order to test custom code that used the
Receipt API. Invoices were created on the fly by the unit tests using
AR interface tables and imported by submitting an Autoinvoice Master
Program. Test receipts against the test invoices were then created using
Receipt API. This was all automated. However, we didn't bother
automating setting up exchange rates and customers.
Which OEBS modules are you using? Perhaps I can talk to some people in
my network for specific pointers.
Hope this helps for now.
Best regards
Finn
By QCTOblog on
Sunday, October 04, 2009
|
Re: Test Approach
Hi Finn,
Thanks for your reply! I'll take note of XML, perhaps it can help.
As for OEBS, I'm interested in testing Fixed Assets. It has necessary
APIs and interface tables, so it's not difficult to, say, create an
asset or make an adjustment. Troubles begin when you need to setup
several assets, then close financial period, then make some adjustments,
then close period again... Surely it can be done, but it requires a lot
of developer's time - which a developer doesn't usually have.
I can think of another approach - searching for a suitable testcase in
existing customer's data. Pro: you haven't to create your own setup,
con: test can be unrepeatable 'cause data changes.
Thanks and regards,
Egor.
By Egor on
Sunday, October 04, 2009
|
|
|